#!bin/bash


if [ -n "$1" ] ; then
  data_date=$1
else
  data_date=`date -d "-1 days" +%F`
fi


DIM_TRAFFIC_SOURCE_TOP10_SQL="
INSERT OVERWRITE TABLE gd2_tmp_hive.dim_traffic_source_top10
SELECT
    t.source_type AS source_type,
    SUM(t.source_uv) AS visitor_count,
    CASE
        WHEN SUM(t.source_uv) = 0 THEN 0
        ELSE ROUND(SUM(s.pay_buyer_count) / SUM(t.source_uv), 4)
        END AS pay_conversion
FROM gd2_tmp_hive.ods_product p
         LEFT JOIN gd2_tmp_hive.tmp_product_sales t
                   ON p.product_id = t.product_id AND p.dt = t.dt
         LEFT JOIN gd2_tmp_hive.fact_product_sales s
                   ON p.product_id = s.product_id AND p.dt = s.dt
WHERE
        p.dt = '2025-07-30'
  AND t.product_id IS NOT NULL
  AND s.product_id IS NOT NULL
GROUP BY t.source_type
ORDER BY visitor_count DESC
LIMIT 10;
"

DIM_SKU_SALES_TOP5_SQL="
INSERT OVERWRITE TABLE gd2_tmp_hive.dim_sku_sales_top5
SELECT
    sku_id,
    sku_attr AS sku_info,
    SUM(sku_sales) AS pay_count,
    MAX(sku_stock) AS current_stock,
    ROUND(SUM(sku_sales) / sub.total_sales, 4) AS proportion,
    CASE
        WHEN SUM(sku_sales) = 0 THEN 0
        ELSE ROUND(MAX(sku_stock) / SUM(sku_sales), 2)
        END AS stock_available_days
FROM gd2_tmp_hive.fact_sku_sales fss
         LEFT JOIN (
    SELECT product_id, SUM(sku_sales) AS total_sales
    FROM gd2_tmp_hive.fact_sku_sales
    WHERE dt = '2025-07-30'
    GROUP BY product_id
) sub ON fss.product_id = sub.product_id
WHERE fss.dt = '2025-07-30'
GROUP BY sku_id, sku_attr, sub.total_sales
ORDER BY pay_count DESC
LIMIT 5;
"

DIM_SEARCH_WORD_TOP10_SQL="
INSERT INTO gd2_tmp_hive.dim_search_word_top10
SELECT
    sw.search_word ,
    SUM(sw.word_uv)
FROM gd2_tmp_hive.ods_product p
         LEFT JOIN gd2_tmp_hive.fact_search_word sw
                   ON p.product_id = sw.product_id AND p.dt = sw.dt
WHERE p.dt = '2025-07-30'
  AND sw.product_id IS NOT NULL
GROUP BY sw.search_word
ORDER BY SUM(sw.word_uv) DESC
LIMIT 10;
"


DIM_PRICE_STRENGTH_DAILY_TOP10_SQL="
WITH daily_rank AS (
    SELECT
        dt,
        product_name,
        price_strength,
        source_uv,
        pay_conversion,
        sales_amount,
        ROW_NUMBER() OVER (
            PARTITION BY dt
            ORDER BY
                CASE price_strength
                    WHEN 'excellent' THEN 3
                    WHEN 'good' THEN 2
                    WHEN 'poor' THEN 1
                    ELSE 0
                    END DESC,
                sales_amount DESC
            ) AS rank
    FROM gd2_tmp_hive.dim_product_360_history
)
INSERT INTO gd2_tmp_hive.dim_price_strength_daily_top10
SELECT
    dt,
    product_name,
    price_strength,
    source_uv,
    pay_conversion,
    sales_amount,
    rank
FROM daily_rank
WHERE rank <= 10
ORDER BY dt ASC, rank ASC;
"

/opt/module/spark/bin/beeline -u jdbc:hive2://node101:10001 -n bwie -p 123456 -e "${DIM_TRAFFIC_SOURCE_TOP10_SQL}${DIM_SKU_SALES_TOP5_SQL}${DIM_SEARCH_WORD_TOP10_SQL}${DIM_PRICE_STRENGTH_DAILY_TOP10_SQL}"

